/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DataServices;

import connection.Configuration;
import java.sql.*;
import java.util.Properties;
import java.util.Vector;

/**
 *
 * @author DOANTUNGANH
 */
// Manage data transaction with database
public class TransactBean {

    private Connection conn = null;
    private ResultSet rs = null;
    private Configuration createConn = null;

    public void setCreateConn(Configuration createConn) {
        this.createConn = createConn;
    }

    public Configuration getCreateConn() {
        return createConn;
    }
    // 3 types of SQL query
    private Statement statement = null;
    private PreparedStatement preparedStatement = null;
    private CallableStatement storeProcedure = null;
    private Properties properties = null;       // properties of query
    private String table = null;                //Name of the table
    private Vector<String> columnsName = null;   // Name of column of a table
    private int error = 0;
    String errorMessage = null;

    public int getError() {
        return error;
    }

    public void setProperties(Properties properties) {
        this.properties = properties;
    }

    public Properties getProperties() {
        return properties;
    }

    public Statement getStatement() {
        createConn = new Configuration();
        conn = createConn.getConnect();
        try {
            statement = conn.createStatement();
        } catch (SQLException ex) {
            error = -1;
            errorMessage = "Error : " + ex.getSQLState() + " -- " + ex.getMessage();
        }
        return statement;
    }

    public PreparedStatement getpreparedStatement(String sql) {
        createConn = new Configuration();
        conn = createConn.getConnect();
        try {
            preparedStatement = conn.prepareStatement(sql);
        } catch (SQLException ex) {
            error = -1;
            errorMessage = "Error : " + ex.getSQLState() + " -- " + ex.getMessage();
        }
        return preparedStatement;
    }

    public CallableStatement getStoreProcedure(String sql) {
        createConn = new Configuration();
        conn = createConn.getConnect();
        try {
            storeProcedure = conn.prepareCall(sql);
        } catch (SQLException ex) {
            error = -1;
            errorMessage = "Error : " + ex.getSQLState() + " -- " + ex.getMessage();
        }
        return storeProcedure;
    }

    public ResultSet getRs() {
        return rs;
    }

    public void setRs(ResultSet rs) {
        this.rs = rs;
    }

    public void destroy() {
        try {
            if (rs != null) {
                rs.close();
            }
            if (statement != null) {
                statement.close();;
            }
            if (preparedStatement != null) {
                preparedStatement.close();
            }
            if (storeProcedure != null) {
                storeProcedure.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException ex) {
            error = -1;
            errorMessage = "Error : " + ex.getSQLState() + " -- " + ex.getMessage();
        }
    }

    public String getTable() {
        return table;
    }

    public void setTable(String table) {
        this.table = table;
    }

    public void setColumnsName(Vector<String> columnsName) {
        this.columnsName = columnsName;
    }

    ///////////////////////////////////////////////////////////////////
    // INSERT DATA TO TABLE
    public void insert(Properties columnsNameParameter) {
        String queryString = "INSERT INTO " + table + " (";
        int flag = 0;
        for (int i = 0; i < columnsName.size(); i++) {
            // take value of column name parameter
            String s = columnsNameParameter.getProperty(columnsName.get(i));
            if (s != null) {
                if (flag != 0) {
                    queryString += ",";
                }
                queryString += columnsName.get(i);
                flag++;
            }
        }
        queryString += ") VALUES (";
        flag = 0;

        for (int i = 0; i < columnsName.size(); i++) {
            String s = columnsNameParameter.getProperty(columnsName.get(i));
            if (s != null) {
                if (flag != 0) {
                    queryString += ",";
                }
                queryString += "'" + columnsNameParameter.getProperty(columnsName.get(i));
                flag++;
            }
        }
        queryString += ");";
        try {
            System.out.println(queryString);
            statement.executeQuery(queryString);
        } catch (SQLException ex) {
            error = -1;
            errorMessage = "Insert transaction error";
        }
    }

    // UPDATE DATA IN TABLE
    public void update(Properties columnsNameParameter) {
        String queryString = "UPDATE " + table + " SET ";
        int flag = 0;
        for (int i = 0; i < columnsName.size(); i++) {
            String s = columnsNameParameter.getProperty(columnsName.get(i));
            if (s != null) {
                if (flag != 0) {
                    queryString += ",";
                }
                queryString += columnsName.get(i) + " = '" + columnsNameParameter.getProperty(columnsName.get(i)) + "'";
                flag++;
            }
        }

        queryString += " WHERE " + columnsName.get(0) + " = " + columnsNameParameter.getProperty(columnsName.get(0)) + "'";
        try {
            System.out.println(queryString);
            statement.executeUpdate(queryString);
        } catch (SQLException ex) {
            error = -1;
            errorMessage = "Update transaction error";
        }
    }

    // DELETE RECORD FROM TABLE
    public void delete(Properties columnsNameParameter) {
        String queryString = "DELETE FROM " + table + " WHERE ";
        int flag = 0;
        for (int i = 0; i < columnsName.size(); i++) {
            String s = columnsNameParameter.getProperty(columnsName.get(i));
            if (s != null) {
                if (flag != 0) {
                    queryString += " AND ";
                }
                queryString += columnsName.get(i) + " = '" + columnsNameParameter.getProperty(columnsName.get(i)) + "'";
                flag++;
            }
        }
        try {
            System.out.println(queryString);
            statement.executeUpdate(queryString);
        } catch (SQLException ex) {
            error = -1;
            errorMessage = "Delete transaction error";
        }
    }
}
